# -*- coding: utf-8 -*-
"""
Created on Tue Apr 17 15:49:51 2018

@author: lwm
"""
import pandas as pd
import numpy as np
import collections
from time import time
import pymysql
from DBUtils.PooledDB import PooledDB
import difflib
import json

MysqlPool = PooledDB(creator=pymysql,  mincached=1, maxcached=6, host="139.196.174.131", port=3306,\
                    user="bozai", passwd="hellobobo", db='easy_refiner_step4_data_files', charset="utf8")

#用于从数据库读取所需数据的代码
def read_data_from_mysql(conn, sqli, col_names=None):
    '''
    #conn: 数据库连接对象
    #sqli: sql语句
    #col_names: 需要提取的字段名
    #return: 数据表中指定字段的数据
    '''
    cursor = conn.cursor(pymysql.cursors.SSCursor)    #使用cursor()方法获取操作游标
    # 查询
    try:
        # print(sqli)
        # 执行SQL语句
        cursor.execute(sqli)
        # 获取所有记录列表
        data_mysql = []
        while True:
            row = cursor.fetchone()
            if not row:
                break
            data_mysql.append(row)
        #print(data_mysql)
        data_mysql = pd.DataFrame(data_mysql, columns=col_names)
        cursor.close()  #关闭指针对象
        return data_mysql
    except Exception as err:
        print(repr(err))
        conn.close()
        print ("Error: unable to fecth data")


#用于读取表格数据，并将数据转换为全为str类型的numpy.matrix数据的函数
#注意读取的文件格式要求是csv
def Get_TableData(original_data):  
    #创建一个变量指向一个字符串，会把所有的空单元格的值替换为该字符串，方便后续的处理
    space_char = ''
    #从dataframe格式的表格转换为了numpy的矩阵
    data = np.array(original_data).astype(str)
    for i in range(len(data)):
        for j in range(len(data[i])): 
            #删去了所有单元格的空格以及替换空表格为变量‘space_char’指向的字符串
            data[i,j] = str(data[i,j]).replace(' ','').replace('nan',space_char)
    return data

#用于判别值是否是浮点数
def Is_Float(CellValue):
    try:
        float(CellValue)
        return True
    except ValueError:
        pass
    return False

#用于判别单元格的值是否是纯数值用的函数
def Is_Num(CellValue):
    if CellValue.isdigit() or Is_Float(CellValue):
        return True
    else:
        return False

#用于获得一列单元格主要的单元格类型，有数值类‘num’和文本类‘txt’
def Get_Attribute(ColumnTable):
    num = 0
    txt = 0
    for CellValue in ColumnTable:
        if CellValue != '':
            if Is_Num(CellValue):
                num += 1
            else:
                txt += 1
    if num >= txt:
        return 'num'
    else:
        return 'txt'

#用于提取文本类型的一列单元格的特征：最常出现的那个值
def Deal_TxT(ColumnTable):
    Repeat = collections.Counter(ColumnTable).most_common()
    if Repeat[0][0] == '':
        MostFrequent = Repeat[1][0]
    else:
        MostFrequent = Repeat[0][0]
    LenList = [len(i) for i in ColumnTable if i != '']
    Mean = sum(LenList)/len(LenList)
    return MostFrequent,Mean

#用于提取纯数值类型的一列单元格的特征，最常出现的值，以及归一化后的均值是多少
def Deal_Num(ColumnTable):
    Repeat = collections.Counter(ColumnTable).most_common()
    if Repeat[0][0] == '':
        MostFrequent = Repeat[1][0]
    else:
        MostFrequent = Repeat[0][0]
    LenList = [len(i) for i in ColumnTable if i != '' and Is_Num(i)]
    Mean = sum(LenList)/len(LenList)
    return MostFrequent,Mean

#用于获取每列数据的特征
#特征包含本身字段名，该列的数据类型
#若该列的数据类型是文本型，那么特征还会包含这一列最常出现的几个的变量
#若该列的数据类型是数值型，那么特征还会包含数值是整数还是浮点数，数值标准化，归一化后的走势以及常数的字符串长度
def Get_Feature(Column_List, Field_list):
    #创建两个列表来存储数据主要为文本和纯数值的列
    FeatureList = []
    for i in range(len(Column_List)):
        #获取一列表格
        ColumnTable = Column_List[i]
        #调用函数获取该列表格的主要单元格类型
        Attribute = Get_Attribute(ColumnTable)
        #文本类
        if Attribute == 'txt':
            MostFrequent,Mean = Deal_TxT(ColumnTable)
            Feature = [Field_list[i],'txt',MostFrequent,Mean]
            FeatureList.append(Feature)
        #纯数值类
        elif Attribute == 'num':
            MostFrequent,Mean = Deal_Num(ColumnTable)
            Feature = [Field_list[i],'num',MostFrequent,Mean]
            FeatureList.append(Feature)           
    return FeatureList

#用于获取有可能是互补列的列的位置
def Prepaer_For_Possible(TableData):
    #记录每列非空值的数量
    ValueNumList = []    
    #存储可能是互补列的列的位置
    SmallList = []
    for i in range(len(TableData[0,:])):
        ColumnTable = TableData[:,i] 
        Count = 0
        for value in ColumnTable:
            if value != '':
                Count += 1
        ValueNumList.append(Count)
    for i in range(len(ValueNumList)-1):
        Value = ValueNumList[i]
        CompareList = ValueNumList[i+1:]
        Small_location = [i] + [j+i+1 for j in range(len(CompareList)) if CompareList[j]+Value <= len(TableData)]
        if len(Small_location) > 1:
            SmallList.append(Small_location)
    return SmallList
        
#用于获取互补率高的列
#互补率的计算方法为 两列单元格一一对比
#只有一列的单元格有值的行数/总行数-两列的单元格都没有值的行数
def Get_Possible(TableData,SmallList):
    Length = len(TableData)
    Possible_List = []
    for LocationList in SmallList:
        New_LocationList = [LocationList[0]]
        Be_Compared_Column = TableData[:,LocationList[0]]
        for i in LocationList[1:]:
            OneSide = 0
            NoneSide = 0
            Compare_Column = TableData[:,i]
            for j in range(Length):
                if Be_Compared_Column[j] != '' and Compare_Column[j] == '':
                    OneSide += 1
                elif Be_Compared_Column[j] == '' and Compare_Column[j] != '':
                    OneSide += 1
                elif Be_Compared_Column[j] == '' and Compare_Column[j] == '':
                    NoneSide += 1
            Percent = OneSide / (Length - NoneSide)
            if Percent > 0.95:
                Current = [i,Percent]
                New_LocationList.append(Current)
        if len(New_LocationList) > 1:
            Possible_List.append(New_LocationList)
    return Possible_List

#依据特征对可能是互补列的列们进行判别
#判别筛选完毕后，返回互补列集合
def Check_Possible(TableData,Possible_List,Field_List):
    Final_TotalList = []
    for Possible in Possible_List:
        #将位置信息，所对应的列和变量名提取出来
        location = [Possible[0]] + [i[0] for i in Possible[1:]]
        Chosed_Column_List = [TableData[:,i] for i in location]
        Chosed_Field_List = [Field_List[i] for i in location]
        #获取各列用于对必用的特征
        FeatureList = Get_Feature(Chosed_Column_List, Chosed_Field_List)
        #将所需的被比较的列的特征提取出来
        Be_Compared_FeatureName = FeatureList[0][0]
        Be_Compared_FeatureType = FeatureList[0][1]
        Be_Compared_Feature1 = FeatureList[0][2]
        Be_Compared_Feature2 = float(FeatureList[0][3])
        #先将被比较列的位置存入
        Final_List = [location[0]]
        #遍历用于比较的列
        for i in range(len(FeatureList[1:])):
            Compare_Column = FeatureList[1:][i]
            #获取用于比较的列的类的数据类型
            Compare_FeatureType = Compare_Column[1]
            #当被比较的列的类的数据类型和比较的数据类型一致且为‘num’时，执行以下比较
            if Compare_FeatureType == Be_Compared_FeatureType and Compare_FeatureType == 'num':
                
                Compare_FeatureName = Compare_Column[0]
                Compare_Feature1 = Compare_Column[2]
                Compare_Feature2 = float(Compare_Column[3])
                
#                detection0 = False
#                detection1 = False
#                detection2 = False
#                
#                if abs((Compare_Feature2 - Be_Compared_Feature2) / Be_Compared_Feature2) < 0.1:
#                    detection2 = True
#                    
#                if abs((float(Compare_Feature1) - float(Be_Compared_Feature1))/float(Be_Compared_Feature1)) < 0.1:
#                    detection1 = True
#                    
#                if  difflib.SequenceMatcher(None,Compare_FeatureName,Be_Compared_FeatureName).quick_ratio() > 0.75:
#                    detection0 = True
                
                detection2 = 1 - abs((Compare_Feature2 - Be_Compared_Feature2) / Be_Compared_Feature2)
                detection1 = 1 - abs((float(Compare_Feature1) - float(Be_Compared_Feature1))/float(Be_Compared_Feature1))
                detection0 = difflib.SequenceMatcher(None,Compare_FeatureName,Be_Compared_FeatureName).quick_ratio()
                    
                Final = Possible[i+1][1]*0.5 + detection0*0.25 + detection1*0.10 + detection2*0.15
                if Final > 0.7:
                    Final_List.append([location[i+1],Final])
            #当被比较的列的类的数据类型和比较的数据类型一致且为‘txt’时，执行以下比较
            if Compare_FeatureType == Be_Compared_FeatureType and Compare_FeatureType == 'txt':
                
                Compare_FeatureName = Compare_Column[0]
                Compare_Feature1 = Chosed_Column_List[i+1]
                Compare_Feature2 = float(Compare_Column[3])

#                detection0 = False
#                detection1 = False
#                detection2 = False
#                
#                if abs((Compare_Feature2 - Be_Compared_Feature2) / Be_Compared_Feature2) < 0.1:
#                    detection2 = True
#                    
#                if Be_Compared_Feature2 in Compare_Feature1:
#                    detection1 = True
#                    
#                if  difflib.SequenceMatcher(None,Compare_FeatureName,Be_Compared_FeatureName).quick_ratio() > 0.75:
#                    detection0 = True
                
                detection2 = 1 - abs((Compare_Feature2 - Be_Compared_Feature2) / Be_Compared_Feature2)
                detection1 = Be_Compared_Feature2 in Compare_Feature1
                detection0 = difflib.SequenceMatcher(None,Compare_FeatureName,Be_Compared_FeatureName).quick_ratio() 
                
                    
                Final = Possible[i+1][1]*0.5 + detection0*0.25 + detection1*0.10 + detection2*0.15
                if Final > 0.7:
                    Final_List.append([location[i+1],Final])
        if len(Final_List) > 1:
            Final_TotalList.append(Final_List)
    return Final_TotalList

#用于规范化最后的输出的函数
def Correct_Form(Column_List,Final_TotalList):
    Right_Form = []
    for Final in Final_TotalList:
        for i in range(len(Final[1:])):
            Info = Final[1:][i]
            Corrent = {"credibility":Info[1], "complementCol":[Column_List[Info[0]],Column_List[Final[0]]]}
            Right_Form.append(Corrent)
    return Right_Form        

#从服务器获取数据
def Get_TableDataFromDB(database_name, table_name, where_cond, connect, info_type):
    if info_type == 'table':
        sql_info = "SELECT %s FROM %s"%('*', database_name+'.'+table_name)
        info = read_data_from_mysql(conn=connect, sqli=sql_info)
    elif info_type =='field':
        sql_info = "SELECT %s FROM %s WHERE %s"%('*', database_name+'.'+table_name, where_cond)
        info = read_data_from_mysql(conn=connect, sqli=sql_info)
    return info




if __name__ == '__main__':
    start_time = time()
        
    #对应表格的基本信息存在一张大表里，最好用用id来定位获取    
    connect = MysqlPool.connection()
    #获取服务器传来的参数，准备链接服务器
    file_id = '6124'
    where_cond = 'step3_file_id = '+ file_id
    project_id = 3841  
    Field_FromDB = Get_TableDataFromDB('easy_refiner_management', 'step3_file', where_cond,connect, 'field')
    file_name = Field_FromDB[2][0]
    
    #获取需要处理的表格的数据行
    Table_info = Get_TableData(Get_TableDataFromDB('easy_refiner_step3_data_files', file_name, where_cond,connect,'table'))
    #获取需要处理的表格的基本信息
    Field_info = json.loads(Field_FromDB[4][0])
    
    #为了方便特征的获取，将获得的数据稍微进行处理和提取
    Field_List = [i['externalName'] for i in Field_info]
    Column_List = [i['internalName'] for i in Field_info]
    TableData = Table_info[:,1:len(Field_List)+1]   
    
    #根据互补率来初步获取可能是互补两列
    SmallList = Prepaer_For_Possible(TableData)
    Possible_List = Get_Possible(TableData,SmallList)
    Final_TotalList =  Check_Possible(TableData,Possible_List,Field_List)
    Right_Form = Correct_Form(Column_List,Final_TotalList)
    cost_time = time()-start_time 

    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    

